var mysql=require('mysql');
var fs = require('fs');
var MysqlHelper=
{
	SqlPool:false,
	InitConnect:function()
	{
		fs.readFile('./Mysql.json','utf8',function(err,data){
		    if(err) throw err;
		    var jsonObj = JSON.parse(data);
		    SqlPool= mysql.createPool({
		      connectionLimit : 3,
			  host : jsonObj.host,
			  port : jsonObj.port,
			  user : jsonObj.user,
			  password :jsonObj.password,
			  database:jsonObj.database,
			  dateStrings:true,
			  supportBigNumbers:jsonObj.supportBigNumbers,
			  multipleStatements:jsonObj.multipleStatements

			});
		});
	},

	SelectTable:function(aTableName,aSelCols,aWhere,aParam,aDataCallback)
	{
		if(SqlPool===false)return false;
		if(aSelCols==="")aSelCols="*";
		if(aWhere==="")aWhere="1=1";
		var selectSQL= ["SELECT "];
		selectSQL.push(aSelCols);
		selectSQL.push(" FROM ");
		selectSQL.push(aTableName);
		selectSQL.push(" WHERE ");
		selectSQL.push(aWhere);
		var runSql=selectSQL.join("");
		SqlPool.getConnection(function (err, conn) {
			if(aParam){
				conn.query(runSql,aParam,function(err,rows){
					if (err) 
					{
						console.log("Con ==> "+err);
						aDataCallback?aDataCallback(false):0;
					}
					var Datas=rows;
					conn.release();
					aDataCallback?aDataCallback(Datas):0;
				});
			}else{
				conn.query(runSql,function(err,rows){
					if (err) 
					{
						console.log("Con ==> "+err);
						aDataCallback?aDataCallback(false):0;
					}
					var Datas=rows;
					conn.release();
					aDataCallback?aDataCallback(Datas):0;
				});
			}
		});
	},

	InsertTableRow:function(aTableName,aInsertCols,aInsertValueArray,aParam,aDataCallback)
	{
		if(SqlPool===false)return false;
		var InsertSQL= ["INSERT INTO "];
		InsertSQL.push(aTableName);
		InsertSQL.push("(");
		for(var i=0; i<aInsertCols.length;i++)
		{
			if(i>0){InsertSQL.push(',');}
			InsertSQL.push(aInsertCols[i]);
		}
		InsertSQL.push(")");
		InsertSQL.push(" VALUES(");
		for(var i=0; i<aInsertValueArray.length;i++)
		{
			if(i>0){InsertSQL.push(',');}
			InsertSQL.push(aInsertValueArray[i]);
		}
		InsertSQL.push(");");
		var runSql=InsertSQL.join('');
		SqlPool.getConnection(function (err, conn) {
			if(aParam){
				conn.query(runSql,aParam,function(err,result){
					if (err) 
					{
						console.log("Con ==> "+err);
						aDataCallback?aDataCallback(-1):0;
					}
					var NewId=result.insertId;
					conn.release();
					aDataCallback?aDataCallback(NewId):0;
				});
			}else{
				conn.query(runSql,function(err,result){
					if (err) 
					{
						console.log("Con ==> "+err);
						aDataCallback?aDataCallback(-1):0;
					}
					var NewId=result.insertId;
					conn.release();
					aDataCallback?aDataCallback(NewId):0;
				});
			}
		});
	},

	DeleteTable:function(aTableName,aWhere,aParam,aDataCallback)
	{
		if(SqlPool===false)return false;
		var runSQL= "DELETE FROM ?? WHERE ";
		runSQL = mysql.format(runSQL, [aTableName]);
		runSQL+=aWhere;
		SqlPool.getConnection(function (err, conn) {
			if(aParam){
				conn.query(runSQL,aParam,function(err,result){
					if (err) 
					{
						console.log("Con ==> "+err);
						aDataCallback?aDataCallback(-1):0;
					}
					var ARows=result.affectedRows;
					conn.release();
					aDataCallback?aDataCallback(ARows):0;
				});
			}else{
				conn.query(runSQL,function(err,result){
					if (err) 
					{
						console.log("Con ==> "+err);
						aDataCallback?aDataCallback(-1):0;
					}
					var ARows=result.affectedRows;
					conn.release();
					aDataCallback?aDataCallback(ARows):0;
				});
			}
		});
	},

	UpdateTable:function(aTableName,aUpdateColArray,aUpdateValueArray,aWhere,aParam,aDataCallback)
	{
		if(SqlPool===false)return false;
		var UpdateSQL= [" UPDATE "];
		UpdateSQL.push(aTableName);
		UpdateSQL.push(" SET ");
		for(var i=0; i<aUpdateColArray.length;i++)
		{
			if(i>0){UpdateSQL.push(',');}
			UpdateSQL.push(aUpdateColArray[i]);
			UpdateSQL.push('=');
			UpdateSQL.push(aUpdateValueArray[i]);
		}
		UpdateSQL.push(" WHERE ");
		UpdateSQL.push(aWhere);
		var runSql=UpdateSQL.join('');
		SqlPool.getConnection(function (err, conn) {
			if(aParam){
				conn.query(runSql,aParam,function(err,result){
					if (err) 
					{
						console.log("Con ==> "+err);
						aDataCallback?aDataCallback(-1):0;
					}
					var ARows=result.affectedRows;
					conn.release();
					aDataCallback?aDataCallback(ARows):0;
				});
			}else{
				conn.query(runSql,function(err,result){
					if (err) 
					{
						console.log("Con ==> "+err);
						aDataCallback?aDataCallback(-1):0;
					}
					var ARows=result.affectedRows;
					conn.release();
					aDataCallback?aDataCallback(ARows):0;
				});
			}
		});
	},

	ExecuteNoqueryCmd:function(aSql,aParam,aDataCallback)
	{
		if(SqlPool===false)return false;
		SqlPool.getConnection(function (err, conn) {
			if(aParam){
				conn.query(aSql,aParam,function(err,result){
					if (err) 
					{
						console.log("Con ==> "+err);
						aDataCallback?aDataCallback(false):0;
					}
					var ARows=result.affectedRows;
					conn.release();
					aDataCallback?aDataCallback(ARows):0;
				});
			}else{
				conn.query(aSql,function(err,result){
					if (err) 
					{
						console.log("Con ==> "+err);
						aDataCallback?aDataCallback(false):0;
					}
					var ARows=result.affectedRows;
					conn.release();
					aDataCallback?aDataCallback(ARows):0;
				});
			}
		});
	},

	BulkInsert:function(aInsertSql,aValues,aDataCallback)
	{
		if(SqlPool===false)return false;
		SqlPool.getConnection(function (err, conn) {
				conn.query(aInsertSql,[aValues],function(err,result){
					if (err) 
					{
						console.log("Con ==> "+err);
						aDataCallback?aDataCallback(false):0;
					}
					var ARows=result.affectedRows;
					conn.release();
					aDataCallback?aDataCallback(ARows):0;
				});
			
		});
	},

	ExecuteQueryCmd:function(aSql,aParam,aDataCallback)
	{
		if(SqlPool===false)return false;
		SqlPool.getConnection(function (err, conn) {
			if(aParam){
				conn.query(aSql,aParam,function(err,rows){
					if (err) 
					{
						console.log("Con ==> "+err);
						aDataCallback?aDataCallback(false):0;
					}
					var Datas=rows;
					conn.release();
					aDataCallback?aDataCallback(Datas):0;
				});
			}else{
				conn.query(aSql,function(err,rows){
					if (err) 
					{
						console.log("Con ==> "+err);
						aDataCallback?aDataCallback(false):0;
					}
					var Datas=rows;
					conn.release();
					aDataCallback?aDataCallback(Datas):0;
				});
			}
		});
	}
}
exports.MysqlHelper=MysqlHelper;